﻿

Public Module AccessHelper
    Function GreateCon() As OleDb.OleDbConnection
        Dim objcn = New OleDb.OleDbConnection
        objcn.ConnectionString = "provider=microsoft.ace.oledb.12.0; data source=UserInfo.accdb"
        objcn.Open()
        Return objcn
    End Function

    ''' <summary>
    ''' 查询是否存在该信息
    ''' </summary>
    ''' <param name="sql"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function QuerySQL(ByVal sql As String) As Boolean
        Dim con As OleDb.OleDbConnection
        Dim com As New OleDb.OleDbCommand
        con = GreateCon()
        com.CommandText = sql
        com.Connection = con
        Dim hasRows As Boolean
        Try
            hasRows = com.ExecuteReader.HasRows
        Catch ex As OleDb.OleDbException

            MessageBox.Show(ex.Message)
        End Try


        If hasRows Then
            con.Close()
            con = Nothing
            Return True
        End If
        con.Close()
        con = Nothing
        Return False
    End Function

    ''' <summary>
    ''' 查询返回DATASET
    ''' </summary>
    ''' <param name="sql"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function ExecuteSQL(ByVal sql As String) As DataSet
        Dim con As OleDb.OleDbConnection
        Dim ds As New DataSet
        con = GreateCon()
        Dim ad As New OleDb.OleDbDataAdapter(sql, con)
        Try
            ad.Fill(ds)
        Catch ex As OleDb.OleDbException
            MessageBox.Show(ex.Message)
        End Try

        Return ds
    End Function
    ''' <summary>
    ''' 执行SQL，增删改
    ''' </summary>
    ''' <param name="sql"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function Execute(ByVal sql As String) As Integer
        Dim con As OleDb.OleDbConnection
        con = GreateCon()
        Dim com As New OleDb.OleDbCommand(sql, con)
        Dim count As New Integer
        Try
            count = com.ExecuteNonQuery()
        Catch ex As OleDb.OleDbException
            MessageBox.Show(ex.ToString)
        Finally
            con.Close()
        End Try
        Return count
    End Function

End Module

